from importlib.resources import read_text

import pymysql

host="localhost"
port=3306
user="root"
password="root"
database="finance"

backup=[]
# 专门处理增，删，改
def update(sql,param):
    con = pymysql.connect(host=host,port=port,user=user,password=password,database=database)
    cursor =  con.cursor()
    cursor.execute(sql,param)
    con.commit()
    cursor.close()
    con.close()

# 专门处理查询
def  select(sql,param):

    con = pymysql.connect(host=host,port=port,user=user,password=password,database=database)
    cursor =  con.cursor()
    cursor.execute(sql,param)
    data =  cursor.fetchall()
    con.commit()
    cursor.close()
    con.close()
    return data

def back_up(table_name):
    #数据库备份
    sql=f"select * from {table_name}"
    con = pymysql.connect(host=host, port=port, user=user, password=password, database=database)
    cursor = con.cursor()
    cursor.execute(sql)
    backup = cursor.fetchall()
    con.commit()
    cursor.close()
    con.close()
    return  backup

def rollback(backup,table_name):

    #数据库恢复
        cols = len(backup[0])
        placeholders = ', '.join(['%s'] *cols)
        sql1 = f"truncate table {table_name}"
        con = pymysql.connect(host=host, port=port, user=user, password=password, database=database)
        cursor = con.cursor()
        cursor.execute(sql1)
        sql2 =  f"INSERT INTO {table_name} VALUES ({placeholders})"
        # 批量执行
        cursor.executemany(sql2, backup)

        con.commit()
        print("恢复成功")

        cursor.close()
        con.close()



if __name__=="__main__":
    sql = "select * from user where username  = %s"
    param = ["lisi2"]
    print(select(sql,param))
    print(len(select(sql,param)))




